//
//  Account.m
//  soccer
//
//  Created by 上官千城 on 15/9/14.
//  Copyright (c) 2015年 上官千城. All rights reserved.
//

#import "Account.h"

@implementation Account

+(instancetype)shareHandle
{
    static Account *user = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        
        user = [[Account alloc]init];
        
    });
    return user;
}

//打开数据库
static sqlite3 *db = nil;
-(void)openDB
{
    if (db != nil) {
        return;
    }
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    NSString *sqlitePath = [filePath stringByAppendingPathComponent:@"dataBase.sqlite"];
    int result = sqlite3_open(sqlitePath.UTF8String, &db);
    NSLog(@"sqlitepath = %@",sqlitePath);
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库");
    }
}

//创建注册页面
-(void)createRegisterTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS Register(number INTEGER PRIMARY KEY AUTOINCREMENT,userName TEXT,password TEXT,telNumber TEXT,userID TEXT,token TEXT,sign TEXT)";
    
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
           NSLog(@"创建表成功");
    }
}
//添加注册用户
-(void)insertRegister:(Register *)reg
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Register (userName, password, telNumber, userID, token, sign) VALUES('%@', '%@', '%@', '%@', '%@', '%@')",reg.userName,reg.password,reg.telNumber,reg.userID,reg.token,reg.sign];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }
}

//根据ID删除对应注册账号
-(void)deleteRegister:(NSString *)personID
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM Register WHERE personID = '%@'",personID];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        //        NSLog(@"删除成功");
    }
}

//选择注册用户
-(NSMutableArray *)selectAllRegister
{
    NSString *sql = @"SELECT * FROM Register";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //查询准备操作
    //参数3.-1代表不限制sql语句长度
    //主要作用是将数据库对象db,sql语句,数据库跟随指针关联到一起,方便数据库查询操作
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //取出表中每行中得每个字段的数据
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *userName =  sqlite3_column_text(stmt, 1);
            const unsigned char *password = sqlite3_column_text(stmt, 2);
            const unsigned char *telNumber = sqlite3_column_text(stmt, 3);
            const unsigned char *userID = sqlite3_column_text(stmt, 4);
            const unsigned char *token = sqlite3_column_text(stmt, 5);
            const unsigned char *sign = sqlite3_column_text(stmt, 6);
            Register *mov = [[Register alloc]init];
            mov.userName = [NSString stringWithUTF8String:(const char *)userName];
            mov.password = [NSString stringWithUTF8String:(const char *)password];
            mov.telNumber = [NSString stringWithUTF8String:(const char *)telNumber];
            mov.userID = [NSString stringWithUTF8String:(const char *)userID];
            mov.token = [NSString stringWithUTF8String:(const char *)token];
            mov.sign = [NSString stringWithUTF8String:(const char *)sign];
            mov.number = number;
            [array addObject:mov];
            
        }
        
        return array;
    }
    else
    {
        NSLog(@"准备失败");
    }
    //释放stmt,清除内存
    sqlite3_finalize(stmt);
    return nil;
}
//删除注册表
-(void)dropRegisterTable
{
    
    NSString *sql = @"DROP TABLE Register";
    int result =  sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }
    
}


//创建个人信息列表
-(void)createMainSettingTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS MainSetting(number INTEGER PRIMARY KEY AUTOINCREMENT,header TEXT,userID TEXT,nickName TEXT,QRcode TEXT,gender TEXT,zone TEXT,signature TEXT)";
    
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }
}
//添加注册用户
-(void)insertMainSetting:(MainSetting *)set
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO MainSetting (header, userID, nickName, QRcode, gender, zone ,signature) VALUES('%@', '%@', '%@', '%@', '%@', '%@', '%@')",set.header,set.userID,set.nickName,set.QRcode,set.gender,set.zone,set.signature];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }
}
//根据ID删除对应注册账号
-(void)deleteMainSetting:(NSString *)userID
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM MainSetting WHERE userID = '%@'",userID];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        //        NSLog(@"删除成功");
    }
}

//选择注册用户
-(NSMutableArray *)selectMainSetting
{
    NSString *sql = @"SELECT * FROM MainSetting";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //查询准备操作
    //参数3.-1代表不限制sql语句长度
    //主要作用是将数据库对象db,sql语句,数据库跟随指针关联到一起,方便数据库查询操作
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //取出表中每行中得每个字段的数据
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *header =  sqlite3_column_text(stmt, 1);
            const unsigned char *userID = sqlite3_column_text(stmt, 2);
            const unsigned char *nickName = sqlite3_column_text(stmt, 3);
            const unsigned char *QRcode = sqlite3_column_text(stmt, 4);
            const unsigned char *gender = sqlite3_column_text(stmt, 5);
            const unsigned char *zone = sqlite3_column_text(stmt, 6);
            const unsigned char *signature = sqlite3_column_text(stmt, 7);
            MainSetting *mov = [[MainSetting alloc]init];
            mov.header = [NSString stringWithUTF8String:(const char *)header];
            mov.userID = [NSString stringWithUTF8String:(const char *)userID];
            mov.nickName = [NSString stringWithUTF8String:(const char *)nickName];
            mov.gender = [NSString stringWithUTF8String:(const char *)gender];
            mov.QRcode = [NSString stringWithUTF8String:(const char *)QRcode];
            mov.zone = [NSString stringWithUTF8String:(const char *)zone];
            mov.signature = [NSString stringWithUTF8String:(const char *)signature];
            mov.number = number;
            [array addObject:mov];
            
        }
        
        return array;
    }
    else
    {
        NSLog(@"准备失败");
    }
    //释放stmt,清除内存
    sqlite3_finalize(stmt);
    return nil;
}
//删除注册表
-(void)dropMainSettingTable
{
    
    NSString *sql = @"DROP TABLE MainSetting";
    int result =  sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }
    
}


//创建偏好设置页面
-(void)createPreferenceTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS Preference(number INTEGER PRIMARY KEY AUTOINCREMENT,photoNum INTEGER,commentNum INTEGER,accept INTEGER,like INTEGER,consultSwitch INTEGER)";
    
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }
}
//添加偏好设置
-(void)insertPreference:(Preference *)pre
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Preference (photoNum, commentNum, accept, like, consultSwitch) VALUES('%ld', '%ld', '%ld', '%ld', '%ld')",pre.photoNum,pre.commentNum,pre.accept,pre.like,pre.consultSwitch];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }
}

//选择偏好设置
-(NSMutableArray *)selectAllPreference
{
    NSString *sql = @"SELECT * FROM Preference";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //查询准备操作
    //参数3.-1代表不限制sql语句长度
    //主要作用是将数据库对象db,sql语句,数据库跟随指针关联到一起,方便数据库查询操作
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //取出表中每行中得每个字段的数据
            int number = sqlite3_column_int(stmt, 0);
            NSInteger photoNum = sqlite3_column_int(stmt, 1);
            NSInteger commentNum = sqlite3_column_int(stmt, 2);
            NSInteger accept = sqlite3_column_int(stmt, 3);
            NSInteger like = sqlite3_column_int(stmt, 4);
            NSInteger consultSwitch = sqlite3_column_int(stmt, 5);
            Preference *mov = [[Preference alloc]init];
            mov.photoNum = photoNum;
            mov.commentNum = commentNum;
            mov.accept = accept;
            mov.like = like;
            mov.consultSwitch = consultSwitch;
            mov.number = number;
            [array addObject:mov];
            
        }
        
        return array;
    }
    else
    {
        NSLog(@"准备失败");
    }
    //释放stmt,清除内存
    sqlite3_finalize(stmt);
    return nil;
}
//删除偏好设置表
-(void)dropPreferenceTable
{
    
    NSString *sql = @"DROP TABLE Preference";
    int result =  sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }
    
}



@end
